﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Entities.Tabs;
using DotNetNuke.Entities.Modules;
using DotNetNuke.Providers.ForumSiteMapProvider.Entities;

namespace DotNetNuke.Providers.ForumSiteMapProvider.Data
{
    internal class SqlDataProvider : DataProvider 
    {
        private const string ModuleQualifier = "fsp_";
        private const string OwnerQualifier = "ifty_";

        #region Private Members
        private const string ProviderType = "data";
        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string _connectionString;
        private string _providerPath;
        private string _objectQualifier;
        private string _databaseOwner;
        #endregion
        #region Constructor
        /// <summary>
        /// Constructs new SqlDataProvider instance
        /// </summary>
        internal SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (_connectionString.Length == 0)
            {
                // Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            //override the standard dotNetNuke qualifier with a iFinity one if it exists
            _objectQualifier = objProvider.Attributes["objectQualifier"];
            if ((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false))
            {
                _objectQualifier += "_";
            }
            else
                if (_objectQualifier == null) _objectQualifier = "";

            _objectQualifier += OwnerQualifier;

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if ((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false))
            {
                _databaseOwner += ".";
            }
        }
        #endregion
        #region Properties

        /// <summary>
        /// Gets and sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }
        /// <summary>
        /// Gets and sets the Provider path
        /// </summary>
        public string ProviderPath
        {
            get { return _providerPath; }
        }
        /// <summary>
        /// Gets and sets the Object qualifier
        /// </summary>
        public string ObjectQualifier
        {
            get { return _objectQualifier; }
        }
        /// <summary>
        /// Gets and sets the database ownere
        /// </summary>
        public string DatabaseOwner
        {
            get { return _databaseOwner; }
        }

        #endregion
        #region private members
        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the fully qualified name of the stored procedure
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <returns>The fully qualified name</returns>
        /// -----------------------------------------------------------------------------
        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }

        private string GetSafeString(SqlDataReader rdr, string name, string defaultValue)
        {
            string result = defaultValue;
            if (Convert.IsDBNull(rdr[name]) == false)
                result = (string)rdr[name];
            return result;

        }
        private int GetSafeInt(SqlDataReader rdr, string name, int defaultValue)
        {
            int result = defaultValue;
            if (Convert.IsDBNull(rdr[name]) == false)
                result = (int)rdr[name];
            return result;
        }
        private DateTime GetSafeDateTime(SqlDataReader rdr, string name, DateTime defaultValue)
        {
            DateTime result = defaultValue;
            if (Convert.IsDBNull(rdr[name]) == false)
                result = (DateTime)rdr[name];
            return result;
        }
        private double GetSafeDouble(SqlDataReader rdr, string name, double defaultValue)
        {
            double result = defaultValue;
            if (Convert.IsDBNull(rdr[name]) == false)
                result = (double)rdr[name];
            return result;
        }
        #endregion


        #region abstract overridden properties

        internal override void GetForumsGroupsAndThreads(int portalId, int moduleId, int fromGroupId, int fromForumId, int fromThreadId, int maxCount, out int totalResults, out int rowsRemaining, out List<GroupInfo> groups, out List<ForumInfo> forums, out List<ThreadInfo> threads)
        {
            totalResults = 0; rowsRemaining = maxCount;
            string sp = GetFullyQualifiedName("GetForumsGroupsAndThreads");
            SqlParameter[] parms = new SqlParameter[6];
            parms[0] = new SqlParameter("@PortalId", portalId);
            parms[1] = new SqlParameter("@moduleId", moduleId);
            parms[2] = new SqlParameter("@fromGroupId", fromGroupId);
            parms[3] = new SqlParameter("@fromForumId", fromForumId);
            parms[4] = new SqlParameter("@fromThreadId", fromThreadId);
            parms[5] = new SqlParameter("@maxCount", maxCount);
            //call the db
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            groups = new List<GroupInfo>();
            forums = new List<ForumInfo>();
            threads = new List<ThreadInfo>();
            while (rdr.Read())
            {
                GroupInfo group = new GroupInfo();
                group.GroupId = GetSafeInt(rdr, "GroupId", -1);
                group.Name = GetSafeString(rdr, "Name", "");
                group.CreatedDate = GetSafeDateTime(rdr, "CreatedDate", DateTime.MinValue);
                group.UpdatedDate = GetSafeDateTime(rdr, "UpdatedDate", DateTime.MinValue);
                groups.Add(group);
            }

            if (rdr.NextResult())
            {
                while (rdr.Read())
                {
                    ForumInfo forum = new ForumInfo();
                    forum.GroupId = GetSafeInt(rdr, "GroupId", -1);
                    forum.ForumId = GetSafeInt(rdr, "ForumId", -1);
                    forum.Name = GetSafeString(rdr, "Name", "");
                    forum.CreatedDate = GetSafeDateTime(rdr, "CreatedDate", DateTime.MinValue);
                    forum.UpdatedDate = GetSafeDateTime(rdr, "UpdatedDate", DateTime.MinValue);
                    forum.SitemapPriority = GetSafeDouble(rdr, "SitemapPriority", 0);
                    forum.LastPostDate = GetSafeDateTime(rdr, "LastPostDate", DateTime.MinValue);
                    forums.Add(forum);
                }
            }
            if (rdr.NextResult())
            {
                while (rdr.Read())
                {
                    ThreadInfo thread = new ThreadInfo();
                    thread.GroupId = GetSafeInt(rdr, "GroupId", -1);
                    thread.ForumId = GetSafeInt(rdr, "ForumId", -1);
                    thread.ThreadId = GetSafeInt(rdr, "ThreadId", -1);
                    thread.Views = GetSafeInt(rdr, "Views", -1);
                    thread.SitemapPriority = GetSafeDouble(rdr, "SitemapPriority", 0);
                    thread.PinnedDate = GetSafeDateTime(rdr, "PinnedDate", DateTime.MinValue);
                    threads.Add(thread);
                }
            }
            if (rdr.NextResult())
            {
                Dictionary<int, ThreadPostInfo> threadPosts = new Dictionary<int, ThreadPostInfo>();
                while (rdr.Read())
                {
                    ThreadPostInfo post = new ThreadPostInfo();
                    post.EarliestPostDate = GetSafeDateTime(rdr, "EarliestPostDate", DateTime.MinValue);
                    post.LatestPostDate = GetSafeDateTime(rdr, "LatestPostDate", DateTime.MinValue);
                    post.NumberPosts = GetSafeInt(rdr, "NumberPosts", 0);
                    int threadId = GetSafeInt(rdr,"ThreadId", -1);
                    if (threadPosts.ContainsKey(threadId) == false)
                    {
                        threadPosts.Add(threadId, post);
                    }
                }
                //now update the threads themselves
                foreach (ThreadInfo thread in threads)
                {
                    int threadId = thread.ThreadId;
                    if (threadPosts.ContainsKey(threadId))
                    {
                        ThreadPostInfo post = threadPosts[threadId];
                        thread.LatestPostDate = post.LatestPostDate;
                        thread.EarliestPostDate = post.EarliestPostDate;
                        thread.NumberPosts = post.NumberPosts;
                    }
                }
            }
            if (rdr.NextResult())
            {
                if (rdr.Read())
                {
                    totalResults = GetSafeInt(rdr, "TotalResults", 0);
                    rowsRemaining = GetSafeInt(rdr, "RowsRemaining", 0);
                }

            }
            rdr.Close();
            rdr.Dispose();
        }
        #endregion
    }
}
